/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.elcom.emocbc.dbmapping.persistence;

import com.elcom.emocbc.dbmapping.tables.BscUpdateCommand;
import com.elcom.emocbc.dbmapping.tables.daos.BscUpdateCommandDao;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.jooq.Configuration;
import org.jooq.ConnectionProvider;
import org.jooq.DSLContext;
import org.jooq.Record2;
import org.jooq.SQLDialect;
import org.jooq.conf.Settings;
import org.jooq.conf.StatementType;
import org.jooq.impl.DSL;
import org.jooq.impl.DefaultConfiguration;

/**
 *
 * @author GiangLT
 */
public class BscUpdateCommandPersistence extends BscUpdateCommandDao {

    public BscUpdateCommandPersistence() {
        ConnectionProvider connectionProvider = new DbConnectionProvider();
        Settings settings = new Settings();
        settings.setStatementType(StatementType.STATIC_STATEMENT);
        Configuration configuration = new DefaultConfiguration()
                .set(connectionProvider)
                .set(SQLDialect.ORACLE)
                .set(settings);
        this.setConfiguration(configuration);
    }
    private BscUpdateCommand BSC_UPDATE_COMMAND = BscUpdateCommand.BSC_UPDATE_COMMAND;

    public int countCommand(String... updateStatus) throws SQLException {
        Connection con = DbAccess.getInstance().getConnection();
        try {
            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
            return context
                    .selectFrom(BSC_UPDATE_COMMAND)
                    .where(BSC_UPDATE_COMMAND.UPDATE_STATUS.in(updateStatus)).fetchCount();
        } finally {
            DbAccess.closeConnection(con);
        }
    }
    
    public Map<String, Integer> countAllUpdateStatus() throws SQLException {
        Connection con = DbAccess.getInstance().getConnection();
        try {
            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
            List<Record2<String, Integer>> records =
                    context.select(BSC_UPDATE_COMMAND.UPDATE_STATUS, DSL.count())
                    .from(BSC_UPDATE_COMMAND)
                    .groupBy(BSC_UPDATE_COMMAND.UPDATE_STATUS)
                    .fetch();
            
            Map<String, Integer> resultMap = new HashMap<String, Integer>(records.size());
            for (Record2<String, Integer> record : records) {
                resultMap.put(record.value1(), record.value2());
            }
            
            return resultMap;
        } finally {
            DbAccess.closeConnection(con);
        }
    }
//
//    public List<BscUpdateCommandRecord> getAllPendingCommand() throws SQLException {
//        Connection con = dbAccess.getConnection();
//        try {
//            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
//            return context
//                    .selectFrom(BSC_UPDATE_COMMAND)
//                    .where(BSC_UPDATE_COMMAND.UPDATE_FLAG.equal(BscUpdateCommandRecord.TO_BE_UPDATED_FLAG)).fetch();
//        } finally {
//            DbAccess.closeConnection(con);
//        }
//    }
//
//    public BscUpdateCommandRecord findPendingCommand(String bscId) throws SQLException {
//        Connection con = dbAccess.getConnection();
//        try {
//            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
//            return context
//                    .selectFrom(BSC_UPDATE_COMMAND)
//                    .where(BSC_UPDATE_COMMAND.UPDATE_FLAG.equal(BscUpdateCommandRecord.TO_BE_UPDATED_FLAG))
//                    .and(BSC_UPDATE_COMMAND.BSC_ID.equal(bscId))
//                    .fetchOne();
//        } finally {
//            DbAccess.closeConnection(con);
//        }
//    }
//
//    public BscUpdateCommandRecord find(String bscId) throws SQLException {
//        Connection con = dbAccess.getConnection();
//        try {
//            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
//            return context
//                    .selectFrom(BSC_UPDATE_COMMAND)
//                    .where(BSC_UPDATE_COMMAND.BSC_ID.equal(bscId))
//                    .fetchOne();
//        } finally {
//            DbAccess.closeConnection(con);
//        }
//    }
//

    public int update(String updateModule) throws SQLException {
        Connection con = this.configuration().connectionProvider().acquire();
        try {
            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
            return context
                    .update(BSC_UPDATE_COMMAND)
                    .set(BSC_UPDATE_COMMAND.UPDATE_MODULE, updateModule)
                    .where(BSC_UPDATE_COMMAND.UPDATE_MODULE.isNull())
                    .and(BSC_UPDATE_COMMAND.UPDATE_FLAG.equal(com.elcom.emocbc.dbmapping.tables.pojos.BscUpdateCommand.TO_BE_UPDATED_FLAG))
                    .execute();
        } finally {
            DbAccess.commitTransaction(con);
            DbAccess.closeConnection(con);
        }
    }
//
//    public int merge(BscUpdateCommandRecord record) throws SQLException {
//        Connection con = dbAccess.getConnection();
//        try {
//            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
//            return context
//                    .mergeInto(BSC_UPDATE_COMMAND)
//                    .using(BSC_UPDATE_COMMAND)
//                    .on(BSC_UPDATE_COMMAND.BSC_ID.equal(record.getBscId()))
//                    .whenMatchedThenUpdate()
//                    .set(BSC_UPDATE_COMMAND.BSC_ID, record.getBscId())
//                    .set(BSC_UPDATE_COMMAND.BSC_NAME, record.getBscName())
//                    .set(BSC_UPDATE_COMMAND.DESCRIPTION, record.getDescription())
//                    .set(BSC_UPDATE_COMMAND.LAST_UPDATED_TIME, record.getLastUpdatedTime())
//                    .set(BSC_UPDATE_COMMAND.TOTAL_CELLS, record.getTotalCells())
//                    .set(BSC_UPDATE_COMMAND.UPDATED_BY, record.getUpdatedBy())
//                    .set(BSC_UPDATE_COMMAND.UPDATE_FLAG, record.getUpdateFlag())
//                    .set(BSC_UPDATE_COMMAND.UPDATE_STATUS, record.getUpdateStatus())
//                    .whenNotMatchedThenInsert()
//                    .set(BSC_UPDATE_COMMAND.ID, record.getId())
//                    .set(BSC_UPDATE_COMMAND.BSC_ID, record.getBscId())
//                    .set(BSC_UPDATE_COMMAND.BSC_NAME, record.getBscName())
//                    .set(BSC_UPDATE_COMMAND.DESCRIPTION, record.getDescription())
//                    .set(BSC_UPDATE_COMMAND.LAST_UPDATED_TIME, record.getLastUpdatedTime())
//                    .set(BSC_UPDATE_COMMAND.TOTAL_CELLS, record.getTotalCells())
//                    .set(BSC_UPDATE_COMMAND.UPDATED_BY, record.getUpdatedBy())
//                    .set(BSC_UPDATE_COMMAND.UPDATE_FLAG, record.getUpdateFlag())
//                    .set(BSC_UPDATE_COMMAND.UPDATE_STATUS, record.getUpdateStatus())
//                    .execute();
//        } finally {
//            DbAccess.commitTransaction(con);
//            DbAccess.closeConnection(con);
//        }
//    }
//
//    @Override
//    protected String getInsertSql(BscUpdateCommandRecord record, DSLContext context) throws SQLException {
//        List values = new ArrayList();
//        values.add(record.getId());
//        values.add(record.getBscId());
//        values.add(record.getBscName());
//        values.add(record.getDescription());
//        values.add(record.getLastUpdatedTime());
//        values.add(record.getTotalCells());
//        values.add(record.getUpdateFlag());
//        values.add(record.getUpdateStatus());
//        values.add(record.getUpdatedBy());
//
//        return context.insertInto(BSC_UPDATE_COMMAND,
//                BSC_UPDATE_COMMAND.ID,
//                BSC_UPDATE_COMMAND.BSC_ID,
//                BSC_UPDATE_COMMAND.BSC_NAME,
//                BSC_UPDATE_COMMAND.DESCRIPTION,
//                BSC_UPDATE_COMMAND.LAST_UPDATED_TIME,
//                BSC_UPDATE_COMMAND.TOTAL_CELLS,
//                BSC_UPDATE_COMMAND.UPDATE_FLAG,
//                BSC_UPDATE_COMMAND.UPDATE_STATUS,
//                BSC_UPDATE_COMMAND.UPDATED_BY).values(values).getSQL(true);
//    }
}
